package com.pic.mgt.dao.haishin;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Collections;
import java.util.Map;
import java.util.Vector;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;

import com.pic.mgt.dao.BusinessMgtDao;
import com.pic.mgt.utils.IgxCollectionUtils;
import com.pic.mgt.utils.SqlScriptUtils;

public final class HaishinBusinessMgtDao
		extends BusinessMgtDao implements IHaishinBusinessMgtDao {
	
	// 資料表名稱
	private final String tableName = "haishin_business_mgt";
	
	private final String insertBasicSqlScript;
	private final String updateStatusAndExecutionTimeByUnitNameSqlScript;
	private final String findRedoAsginTerminalAndRedoAsginTerminalFileByUnitNameSqlScritp;
	private final String findHaishinBusinessMgtInfoByUnitNameSqlScript;
	private final String updateRedoAsginTerminalsByUnitNameSqlScritp;
	private final String updateStatusRedoAsginTerminalFileAndRedoAsginTerminalsByUnitNameSqlScritp;
	
	public HaishinBusinessMgtDao() {
		super();
		
		// 建立 Insert Basic 的 Sql Scritp
		insertBasicSqlScript = createInsertBasicSQLScript();
		
		// 建立 Update Status 和 Execution_Time 的 Sql Script
		updateStatusAndExecutionTimeByUnitNameSqlScript = 
						   createUpdateStatusAndExecutionTimeByUnitNameSqlScript();
		
		// 建立 find redo asgin terminal 和 redo asgin terminal file by unit name 的 sql script
		findRedoAsginTerminalAndRedoAsginTerminalFileByUnitNameSqlScritp = createFindRedoAsginTerminalAndRedoAsginTerminalFileByUnitNameSqlScritp();
		
		// 建立 find haishin business mgt info by unit name 的 sql script
		this.findHaishinBusinessMgtInfoByUnitNameSqlScript = createFindHaishinBusinessMgtInfoByUnitNameSqlScript();
		
		// 建立 update redo asgin terminals by unit name 的 sql script
		updateRedoAsginTerminalsByUnitNameSqlScritp = createUpdateRedoAsginTerminalsByUnitName();
		
		// 建立 update redo asgin terminal, redo asgin terminal file 和 status by unit name 的 sql script
		updateStatusRedoAsginTerminalFileAndRedoAsginTerminalsByUnitNameSqlScritp =
										createUpdateStatusRedoAsginTerminalFileAndRedoAsginTerminalsByUnitNameSqlScritp();
		
	}
	
	private String createFindHaishinBusinessMgtInfoByUnitNameSqlScript() {
		Vector<String> selectFieldsVector =
									new Vector<String>();

		selectFieldsVector.add(_confirmationFile);
		selectFieldsVector.add(_targetFile);
		selectFieldsVector.add(_type);
		selectFieldsVector.add(_targetFilesize);
		selectFieldsVector.add(_status);
		selectFieldsVector.add(_scheduleExecutionTime);
		selectFieldsVector.add(_executionTime);
		selectFieldsVector.add(_loadingTime);
		selectFieldsVector.add(_unitName);
		selectFieldsVector.add(_redoAsginTerminal);
		selectFieldsVector.add(_redoAsginTerminalFile);

		return SqlScriptUtils.createNamedParamQueryScriptWithSingleCondition(
				tableName, selectFieldsVector, _unitName);
	}
	
	public String createUpdateStatusRedoAsginTerminalFileAndRedoAsginTerminalsByUnitNameSqlScritp() {
		Vector<String> updateFieldsVector = new Vector<String>();
		
		updateFieldsVector.add(_redoAsginTerminal);
		updateFieldsVector.add(_redoAsginTerminalFile);
		updateFieldsVector.add(_status);
		
		return SqlScriptUtils.createNamedParamUpdateScript(
										tableName,
										updateFieldsVector,
										_unitName);
	}
	
	public String createUpdateRedoAsginTerminalsByUnitName() {
		Vector<String> updateFieldsVector = new Vector<String>();
		
		updateFieldsVector.add(_redoAsginTerminal);
		
		return SqlScriptUtils.createNamedParamUpdateScript(
										tableName,
										updateFieldsVector,
										_unitName);
	}
	
	public String createFindRedoAsginTerminalAndRedoAsginTerminalFileByUnitNameSqlScritp() {
		Vector<String> selectFieldsVector =
			new Vector<String>();

		selectFieldsVector.add(_redoAsginTerminal);
		selectFieldsVector.add(_redoAsginTerminalFile);

		return SqlScriptUtils.createNamedParamQueryScriptWithSingleCondition(
				tableName, selectFieldsVector, _unitName);
	}
	
	private String createUpdateStatusAndExecutionTimeByUnitNameSqlScript() {
		Vector<String> updateFieldsVector = new Vector<String>();
		
		updateFieldsVector.add(IHaishinBusinessMgtDao._status);
		updateFieldsVector.add(IHaishinBusinessMgtDao._executionTime);
		
		return SqlScriptUtils.createNamedParamUpdateScript(
										tableName,
										updateFieldsVector,
										IHaishinBusinessMgtDao._unitName);
	}
	
	private String createInsertBasicSQLScript() {
		Vector<String> insertFieldsVector =
							new Vector<String>();
		
		// 要新增的欄位
		insertFieldsVector.add(HaishinBusinessMgtDao._confirmationFile);
		insertFieldsVector.add(HaishinBusinessMgtDao._targetFile);
		insertFieldsVector.add(HaishinBusinessMgtDao._type);
		insertFieldsVector.add(HaishinBusinessMgtDao._targetFilesize);
		insertFieldsVector.add(HaishinBusinessMgtDao._status);
		insertFieldsVector.add(HaishinBusinessMgtDao._scheduleExecutionTime);
		insertFieldsVector.add(HaishinBusinessMgtDao._unitName);
		
		return SqlScriptUtils.createNamedParamInsertScript(this.tableName,
											     insertFieldsVector);
	}
	
	@Override
	public int insertBasic(Map<String, Object> insertValuesMap)
												throws DataAccessException{
		if (null != insertValuesMap && !insertValuesMap.isEmpty()) {
			return
			super.namedParameterJdbcTemplate.update(this.insertBasicSqlScript,
													insertValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public int updateStatusAndExecutionTimeByUnitName(String statusValue,
			Timestamp executionTimeValue, String unitName)
			throws DataAccessException {
		if (null != statusValue && null != unitName) {
			Map<String, Object> updateValuesMap =
				IgxCollectionUtils.convertToMap(
						new Object[] {IHaishinBusinessMgtDao._status, statusValue,
									  IHaishinBusinessMgtDao._executionTime, executionTimeValue,
									  "__ConditionValue", unitName});
		
		return
			super.namedParameterJdbcTemplate.update(
							this.updateStatusAndExecutionTimeByUnitNameSqlScript,
							updateValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public String[] findRedoAsginTerminalAndRedoAsginTerminalFileByUnitName(String unitName)
			throws DataAccessException {
		try {
			return
			super.namedParameterJdbcTemplate.queryForObject(
					this.findRedoAsginTerminalAndRedoAsginTerminalFileByUnitNameSqlScritp,
					Collections.singletonMap((String)_unitName, (Object)unitName),
					new RowMapper<String[]>() {
						public String[] mapRow(ResultSet rs, int rowNum) throws SQLException {
							String[] redoAsginInfo = new String[2];
							
							redoAsginInfo[0] = rs.getString(_redoAsginTerminal);
							redoAsginInfo[1] = rs.getString(_redoAsginTerminalFile);
							       
							return redoAsginInfo;
						}
					}
				);
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw e;
		}
	}
	
	@Override
	public int updateRedoAsginTerminalByUnitName(String redoAsginTerminal,
			String unitName) throws DataAccessException {
		if (null != unitName) {
			Map<String, Object> updateValuesMap =
				IgxCollectionUtils.convertToMap(
						new Object[] {_redoAsginTerminal, redoAsginTerminal,
									  "__ConditionValue", unitName});
		
		return
			super.namedParameterJdbcTemplate.update(
							this.updateRedoAsginTerminalsByUnitNameSqlScritp,
							updateValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public int updateStatusRedoAsginTerminalFileAndRedoAsginTerminalByUnitName(
								String status, String redoAsginTerminal,
								String redoAsginTerminalFile, String unitName)
			throws DataAccessException {
		if (null != status && null != unitName) {
			Map<String, Object> updateValuesMap =
				IgxCollectionUtils.convertToMap(
						new Object[] {_status, status,
									  _redoAsginTerminal, redoAsginTerminal,
									  _redoAsginTerminalFile, redoAsginTerminalFile,
									  "__ConditionValue", unitName});
		
		return
			super.namedParameterJdbcTemplate.update(
							this.updateStatusRedoAsginTerminalFileAndRedoAsginTerminalsByUnitNameSqlScritp,
							updateValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public HaishinBusinessMgtInfoBean findHaishinBusinessMgtInfoByUnitName(
			String unitName) throws DataAccessException {
		try {
			return
			super.namedParameterJdbcTemplate.queryForObject(
					this.findHaishinBusinessMgtInfoByUnitNameSqlScript,
					Collections.singletonMap((String)_unitName, (Object)unitName),
					new RowMapper<HaishinBusinessMgtInfoBean>() {
						public HaishinBusinessMgtInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
							HaishinBusinessMgtInfoBean haishinBusinessMgtInfoBean = new HaishinBusinessMgtInfoBean();
						
							haishinBusinessMgtInfoBean.setConfirmationFile(rs.getString(_confirmationFile));
							haishinBusinessMgtInfoBean.setTargetFile(rs.getString(_targetFile));
							haishinBusinessMgtInfoBean.setTargetFilesize(rs.getLong(_targetFilesize));
							haishinBusinessMgtInfoBean.setType(rs.getString(_type));
							haishinBusinessMgtInfoBean.setStatus(rs.getString(_status));
							haishinBusinessMgtInfoBean.setUnitName(rs.getString(_unitName));
							haishinBusinessMgtInfoBean.setScheduleExecutionTime(new Timestamp(rs.getDate(_scheduleExecutionTime).getTime()));
							haishinBusinessMgtInfoBean.setLoadingTime(new Timestamp(rs.getDate(_loadingTime).getTime()));
							haishinBusinessMgtInfoBean.setRedoAsginTerminal(rs.getString(_redoAsginTerminal));
							haishinBusinessMgtInfoBean.setRedoAsginTerminalFile(rs.getString(_redoAsginTerminalFile));
							
							Timestamp executionTimestamp = rs.getTimestamp(_executionTime);
							if (null != executionTimestamp) {
								haishinBusinessMgtInfoBean.setLoadingTime(new Timestamp(rs.getDate(_executionTime).getTime()));
							}
							else {
								haishinBusinessMgtInfoBean.setExecutionTime(null);
							}
							
							return haishinBusinessMgtInfoBean;
						}
					}
				);
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw e;
		}
	}
	
	@Override
	public String getTableName() {
		return this.tableName;
	}
}
